package com.rickpan.repository;

import com.rickpan.entity.WorkStatistics;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

/**
 * 工作统计数据访问层
 */
@Repository
public interface WorkStatisticsRepository extends JpaRepository<WorkStatistics, Long> {

    /**
     * 根据用户ID和统计日期查询统计数据
     */
    Optional<WorkStatistics> findByUserIdAndStatDateAndStatType(Long userId, LocalDate statDate, WorkStatistics.StatType statType);

    /**
     * 根据用户ID查询指定日期范围的统计数据
     */
    List<WorkStatistics> findByUserIdAndStatDateBetweenOrderByStatDateDesc(Long userId, LocalDate startDate, LocalDate endDate);

    /**
     * 根据用户ID和统计类型查询统计数据
     */
    Page<WorkStatistics> findByUserIdAndStatTypeOrderByStatDateDesc(Long userId, WorkStatistics.StatType statType, Pageable pageable);

    /**
     * 获取用户最近的统计数据
     */
    List<WorkStatistics> findTop30ByUserIdAndStatTypeOrderByStatDateDesc(Long userId, WorkStatistics.StatType statType);

    /**
     * 获取用户指定时间范围内的日统计数据
     */
    @Query("SELECT ws FROM WorkStatistics ws WHERE ws.userId = :userId " +
           "AND ws.statType = 'DAILY' " +
           "AND ws.statDate BETWEEN :startDate AND :endDate " +
           "ORDER BY ws.statDate ASC")
    List<WorkStatistics> getDailyStatsByDateRange(@Param("userId") Long userId, 
                                                 @Param("startDate") LocalDate startDate, 
                                                 @Param("endDate") LocalDate endDate);

    /**
     * 获取用户指定时间范围内的周统计数据
     */
    @Query("SELECT ws FROM WorkStatistics ws WHERE ws.userId = :userId " +
           "AND ws.statType = 'WEEKLY' " +
           "AND ws.statDate BETWEEN :startDate AND :endDate " +
           "ORDER BY ws.statDate ASC")
    List<WorkStatistics> getWeeklyStatsByDateRange(@Param("userId") Long userId, 
                                                  @Param("startDate") LocalDate startDate, 
                                                  @Param("endDate") LocalDate endDate);

    /**
     * 获取用户指定时间范围内的月统计数据
     */
    @Query("SELECT ws FROM WorkStatistics ws WHERE ws.userId = :userId " +
           "AND ws.statType = 'MONTHLY' " +
           "AND ws.statDate BETWEEN :startDate AND :endDate " +
           "ORDER BY ws.statDate ASC")
    List<WorkStatistics> getMonthlyStatsByDateRange(@Param("userId") Long userId, 
                                                   @Param("startDate") LocalDate startDate, 
                                                   @Param("endDate") LocalDate endDate);

    /**
     * 统计用户总工作记录数
     */
    @Query("SELECT COALESCE(SUM(ws.totalRecords), 0) FROM WorkStatistics ws " +
           "WHERE ws.userId = :userId AND ws.statType = 'DAILY'")
    Long getTotalWorkRecordsByUser(@Param("userId") Long userId);

    /**
     * 统计用户总工时
     */
    @Query("SELECT COALESCE(SUM(ws.totalTimeSpent), 0) FROM WorkStatistics ws " +
           "WHERE ws.userId = :userId AND ws.statType = 'DAILY'")
    Long getTotalTimeSpentByUser(@Param("userId") Long userId);

    /**
     * 获取用户平均完成率
     */
    @Query("SELECT COALESCE(AVG(ws.completionRate), 0) FROM WorkStatistics ws " +
           "WHERE ws.userId = :userId AND ws.statType = 'DAILY' " +
           "AND ws.statDate >= :startDate")
    Double getAvgCompletionRateByUser(@Param("userId") Long userId, @Param("startDate") LocalDate startDate);

    /**
     * 获取用户平均效率评分
     */
    @Query("SELECT COALESCE(AVG(ws.efficiencyScore), 0) FROM WorkStatistics ws " +
           "WHERE ws.userId = :userId AND ws.statType = 'DAILY' " +
           "AND ws.statDate >= :startDate")
    Double getAvgEfficiencyScoreByUser(@Param("userId") Long userId, @Param("startDate") LocalDate startDate);

    /**
     * 获取用户工作趋势数据（按周统计）
     */
    @Query("SELECT ws.statDate, ws.totalRecords, ws.completedRecords, ws.totalTimeSpent, ws.efficiencyScore " +
           "FROM WorkStatistics ws " +
           "WHERE ws.userId = :userId AND ws.statType = 'WEEKLY' " +
           "AND ws.statDate >= :startDate " +
           "ORDER BY ws.statDate ASC")
    List<Object[]> getWorkTrendsByWeek(@Param("userId") Long userId, @Param("startDate") LocalDate startDate);

    /**
     * 获取用户工作趋势数据（按月统计）
     */
    @Query("SELECT ws.statDate, ws.totalRecords, ws.completedRecords, ws.totalTimeSpent, ws.efficiencyScore " +
           "FROM WorkStatistics ws " +
           "WHERE ws.userId = :userId AND ws.statType = 'MONTHLY' " +
           "AND ws.statDate >= :startDate " +
           "ORDER BY ws.statDate ASC")
    List<Object[]> getWorkTrendsByMonth(@Param("userId") Long userId, @Param("startDate") LocalDate startDate);

    /**
     * 获取用户分类统计数据
     */
    @Query("SELECT ws.categoryStats FROM WorkStatistics ws " +
           "WHERE ws.userId = :userId AND ws.statType = 'DAILY' " +
           "AND ws.statDate >= :startDate " +
           "AND ws.categoryStats IS NOT NULL")
    List<String> getCategoryStatsByUser(@Param("userId") Long userId, @Param("startDate") LocalDate startDate);

    /**
     * 获取用户优先级统计数据
     */
    @Query("SELECT ws.priorityStats FROM WorkStatistics ws " +
           "WHERE ws.userId = :userId AND ws.statType = 'DAILY' " +
           "AND ws.statDate >= :startDate " +
           "AND ws.priorityStats IS NOT NULL")
    List<String> getPriorityStatsByUser(@Param("userId") Long userId, @Param("startDate") LocalDate startDate);

    /**
     * 删除指定日期之前的统计数据（用于数据清理）
     */
    void deleteByUserIdAndStatDateBefore(Long userId, LocalDate beforeDate);

    /**
     * 获取需要生成统计数据的日期列表
     */
    @Query("SELECT DISTINCT wr.date FROM WorkRecord wr " +
           "WHERE wr.userId = :userId " +
           "AND wr.date NOT IN (SELECT ws.statDate FROM WorkStatistics ws " +
           "                   WHERE ws.userId = :userId AND ws.statType = 'DAILY') " +
           "ORDER BY wr.date DESC")
    List<LocalDate> getMissingStatDates(@Param("userId") Long userId);

    /**
     * 检查是否存在指定用户的统计数据
     */
    boolean existsByUserId(Long userId);

    /**
     * 获取用户最新的统计数据
     */
    Optional<WorkStatistics> findTopByUserIdAndStatTypeOrderByStatDateDesc(Long userId, WorkStatistics.StatType statType);
}
